// 管理右侧4个  代
var express = require('express');
var router = express.Router();
const db = require('../../db/db');


//牲畜编号列表
router.get('/getCattleList', function(req, res, next) {
    const sql = `SELECT F_Id ,F_IMEI FROM nc_livestock`;
     console.log(sql);
    db.query(sql, (err, result) => {
        if (err) {
            res.send({ code: 0, message: '获取失败' });
            return;
        } else {
            res.send({ code: 200, message: '获取成功', data: result });
        }
    })
});


/****************************疫苗管理***************************/

// 疫苗列表和符合条件的 F_IMEI (分页)
router.get('/getVaccineList', function(req, res, next) {
  const page = parseInt(req.query.page) || 1; // 当前页码，默认为 1
  const pageSize = parseInt(req.query.pageSize) || 10; // 每页数量，默认为 10
  const offset = (page - 1) * pageSize; // 计算 OFFSET

  // SQL 查询疫苗列表 (分页)
  const sqlVaccineList = `
    SELECT * FROM nc_vaccine_log
    LIMIT ? OFFSET ?
  `;

  // SQL 查询符合条件的 F_IMEI
  const sqlIMEI = `
    SELECT l.F_IMEI, v.F_Id AS F_vaccine_log_Id
    FROM nc_livestock l
    INNER JOIN nc_vaccine_log v ON l.F_Id = v.F_liveid
  `;

  // SQL 查询总记录数
  const sqlCount = `SELECT COUNT(*) AS total FROM nc_vaccine_log`;

  const queryIMEI = () => {
    return new Promise((resolve, reject) => {
      db.query(sqlIMEI, (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  };

  const queryVaccineList = () => {
    return new Promise((resolve, reject) => {
      db.query(sqlVaccineList, [pageSize, offset], (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  };

  const queryTotalCount = () => {
    return new Promise((resolve, reject) => {
      db.query(sqlCount, (err, result) => {
        if (err) {
          reject(err);
        } else {
          const total = result[0].total;
          resolve(total);
        }
      });
    });
  };

  // 使用 Promise.all 来并行执行三个查询
  Promise.all([
    queryIMEI(),
    queryVaccineList(),
    queryTotalCount()
  ])
  .then(([imeiList, vaccineList, total]) => {
    const combinedData = vaccineList.map(vaccine => {
      // 查找与当前疫苗记录关联的 F_IMEI
      const imei = imeiList.find(imei => imei.F_vaccine_log_Id === vaccine.F_Id);

      return {
        ...vaccine,
        F_IMEI: imei ? imei.F_IMEI : null  //  添加 F_IMEI，如果不存在则为 null
      };
    });

    res.send({
      code: 200,
      message: '获取成功',
      data: combinedData,
      page: page,
      pageSize: pageSize,
      total: total  //  添加总记录数
    });
  })
  .catch(error => {
    console.error('Error querying data: ' + error.stack);
    res.send({ code: 0, message: '获取失败', error: error });
  });
});


//新增疫苗记录
router.post('/addVaccine', function(req, res, next) {
   const { F_liveid, F_Title, F_Address,F_Name,F_Batch,F_PHone,F_Number,F_Remark} = req.body;

    if (!F_liveid || !F_Title || !F_Address || !F_Name || !F_Batch || !F_PHone || !F_Number || !F_Remark) {
       return res.status(400).send({ code: 0, message: '参数错误' }); //  使用 400 Bad Request
   }

   const F_Id = Date.now(); //  生成 F_Id

   //  构建 SQL 查询语句 (使用参数化查询)
   const sql = `
       INSERT INTO nc_vaccine_log (
           F_Id, F_liveid, F_Title, F_Address, F_Name, F_Batch, F_PHone, F_Number, F_Remark
       ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
   `;

   //  构建参数数组
   const values = [
       F_Id,
       F_liveid,
       F_Title,
       F_Address,
       F_Name,
       F_Batch,
       F_PHone,
       F_Number,
       F_Remark,
   ];

   console.log(sql); 

   //  执行查询
   db.query(sql, values, (err, result) => {
       if (err) {
           console.error("数据库插入错误:", err);  // 打印错误日志
           return res.status(500).send({ code: 0, message: '添加失败', error: err.message }); 
       } else {
           console.log("添加成功:", result); // 打印成功日志
           return res.status(200).send({ code: 200, message: '添加成功', data: [] }); 
       }
   });
});
/****************************配种管理***************************/
// 配种列表 (分页)
router.get('/getBreed', function(req, res, next) {
const page = parseInt(req.query.page) || 1; // 当前页码，默认为 1
const pageSize = parseInt(req.query.pageSize) || 10; // 每页数量，默认为 10
const offset = (page - 1) * pageSize; // 计算 OFFSET

// SQL 查询配种列表 (分页)
const sqlBreedList = `
 SELECT * FROM nc_descent
 LIMIT ? OFFSET ?
`;
// SQL 查询符合条件的 F_IMEI (父系)
const sqlIMEI1 = `
 SELECT l.F_IMEI, d.F_Id AS F_descent_Id
 FROM nc_livestock l
 INNER JOIN nc_descent d ON l.F_Id = d.F_PaternalLine
`;
// SQL 查询符合条件的 F_IMEI (母系)
const sqlIMEI2 = `
 SELECT l.F_IMEI, d.F_Id AS F_descent_Id
 FROM nc_livestock l
 INNER JOIN nc_descent d ON l.F_Id = d.F_Matriarchal
`;

// SQL 查询总记录数
const sqlCount = `SELECT COUNT(*) AS total FROM nc_descent`;

const queryIMEI1 = () => {
 return new Promise((resolve, reject) => {
   db.query(sqlIMEI1, (err, result) => {
     if (err) {
       reject(err);
     } else {
       resolve(result);
     }
   });
 });
};
const queryIMEI2 = () => {
 return new Promise((resolve, reject) => {
   db.query(sqlIMEI2, (err, result) => {
     if (err) {
       reject(err);
     } else {
       resolve(result);
     }
   });
 });
};
// 使用 Promise 封装数据库查询
const queryBreedList = () => {
 return new Promise((resolve, reject) => {
   db.query(sqlBreedList, [pageSize, offset], (err, result) => {
     if (err) {
       reject(err);
     } else {
       resolve(result);
     }
   });
 });
};

const queryTotalCount = () => {
 return new Promise((resolve, reject) => {
   db.query(sqlCount, (err, result) => {
     if (err) {
       reject(err);
     } else {
       const total = result[0].total;
       resolve(total);
     }
   });
 });
};

// 使用 Promise.all 并行执行查询
Promise.all([
 queryBreedList(),
 queryTotalCount(),
 queryIMEI1(),
 queryIMEI2(),
])
.then(([breedList, total, imeiList1, imeiList2]) => {
 const combinedData = breedList.map(breed => {
   let paternalIMEI = null;
   let matriarchalIMEI = null;

   // 查找父系 F_IMEI
   const paternalImeiData = imeiList1.find(imei => imei.F_descent_Id === breed.F_Id);
   if (paternalImeiData) {
     paternalIMEI = paternalImeiData.F_IMEI;
   }

   // 查找母系 F_IMEI
   const matriarchalImeiData = imeiList2.find(imei => imei.F_descent_Id === breed.F_Id);
   if (matriarchalImeiData) {
     matriarchalIMEI = matriarchalImeiData.F_IMEI;
   }

   return {
     ...breed,
     F_Paternal_IMEI: paternalIMEI || null, // 添加父系 F_IMEI，如果不存在则为 null
     F_Matriarchal_IMEI: matriarchalIMEI || null, // 添加母系 F_IMEI，如果不存在则为 null
   };
 });

 res.send({
   code: 200,
   message: '获取成功',
   data: combinedData,
   page: page,
   pageSize: pageSize,
   total: total, // 添加总记录数
 });
})
.catch(error => {
 console.error('Error querying data: ' + error.stack);
 res.send({ code: 0, message: '获取失败', error: error });
});
});

//新增配种
router.post('/addBreed', function(req, res, next) {
   const { F_UserName, F_Phone, F_Address,F_PaternalLine,F_Matriarchal,F_Remark} = req.body;

    if (!F_UserName || !F_Phone || !F_Address || !F_PaternalLine || !F_Matriarchal || !F_Remark) {
       return res.status(400).send({ code: 0, message: '参数错误' }); 
   }

   const F_Id = Date.now(); //  生成 F_Id

   //  构建 SQL 查询语句 (使用参数化查询)
   const sql = `
       INSERT INTO nc_descent (
           F_Id, F_UserName, F_Phone, F_Address, F_PaternalLine, F_Matriarchal, F_Remark
       ) VALUES (?, ?, ?, ?, ?, ?, ?)
   `;

   //  构建参数数组
   const values = [
       F_Id,
       F_UserName,
       F_Phone,
       F_Address,
       F_PaternalLine,
       F_Matriarchal,
       F_Remark,
   ];

   console.log(sql); 

   //  执行查询
   db.query(sql, values, (err, result) => {
       if (err) {
           console.error("数据库插入错误:", err);  // 打印错误日志
           return res.status(500).send({ code: 0, message: '添加失败', error: err.message }); 
       } else {
           console.log("添加成功:", result); // 打印成功日志
           return res.status(200).send({ code: 200, message: '添加成功', data: [] }); 
       }
   });
});


module.exports = router;